/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package kn.mx.cstf2.sql;

import java.sql.SQLException;

import javax.management.Query;

/**
 *
 * @author user1
 */
public class TransitionSql {
    public static final String SELECCIONAR_DATAFROMKNKEYFROMSTK;
    public static final String SELECCIONAR_IFOWNERFROMKNKEY;
    public static final String SELECCIONAR_IDKNKEY;
    public static final String SELECCIONAR_DATATRANSITION;
    public static final String SELECCIONAR_DOCUMENTNAME;
    public static final String SELECCIONAR_JOINDOCUMENTTRANSITIONDATA;
    public static final String SELECCIONAR_DATAPERSONAL;
    public static final String SELECCIONAR_IDFROMDOCUMENTSBYNAME;
    public static final String SELECCIONAR_IDSFROMJOINDOCUMENTTRANSITION;
    public static final String INSERTAR_RELACION_EN_JOINDOCUMENTTRANSITION;
    public static final String UPDATE_VOBO_EN_JOINDOCUMENTTRANSITION;
    public static final String UPDATE_VOBO_CHECKBOX;
    public static final String UPDATE_LOGTRANSITION;
    public static final String UPDATE_STATUS;
    public static final String UPDATE_STATUS_IMPLE;
    public static final String SELECCIONAR_STATUSTRANSITION;
    public static final String INSERTAR_PERSONAL;
    public static final String SELECCIONAR_NOMBRE_PERSONAL;
    public static final String SELECCIONAR_DATAPERSONAL_BYNAME;
    public static final String INSERT_JOINPERSONALTRANSITION;
    public static final String SELECCIONAR_DATAPERSONAL_BY_EMAIL;
    public static final String ELIMINAR_PERSONAL_BY_ID;
    public static final String ELIMINAR_JOINPERSONTRANSITION;
    public static final String VERIFICA_COUNT_RELACION_JOINPERSONTRANSITION;
    public static final String OBTENER_TRANSITION_LOG;
    public static final String SELECCIONAR_DATATRANSITIONIDKNKEY;
    public static final String SELECCIONAR_STATUSIMPLEMENTATION;
    public static final String SELECCIONAR_NOMBRE_PERSONAL_BYKNKEY_TRANSITION;
    public static final String SELECCIONAR_DATAPERSONAL_BYKNKEY;
    public static final String SELECCIONAR_DATAPERSONAL_BYIDP;
    public static final String SELECCIONAR_DATAPERSONAL_BYKNID;
    public static final String SELECT_VOBO_EN_JOINDOCUMENTTRANSITION;
    public static final String UPDATE_DETAILS_EN_JOINDOCUMENTTRANSITION;
    public static final String SELECCIONAR_JOINDOCUMENTTRANSITIONDETAIL;
    public static final String SELECCIONAR_FILES_UPLOADED;
    public static final String UPDATE_LAST_DATE_COMMENT;
    public static final String OBTENER_ULTIMA_FECHA_ESTATUS;
    public static final String OBTENER_ULTIMA_FECHA_COMMENT;
    public static final String UPDATE_LAST_UPDATE_REQUI;
    public static final String OBTENER_ULTIMA_FECHA_REQUI;
    public static final String CHECAR_EXISTENCIA_TRANSITION;
    public static final String UPDATE_UPLOAD_FILE;
    
     static {
        StringBuilder qryBuilder = new StringBuilder();
        qryBuilder.append("SELECT companyname, comercialname, compaddress, ciudad "+
            "FROM t_clientes "+            
            " WHERE "+
            " knkey = ? ");
        SELECCIONAR_DATAFROMKNKEYFROMSTK = qryBuilder.toString();
        
                
        qryBuilder = new StringBuilder();
        qryBuilder.append(" SELECT tp.id, tp.bf, tp.name, tp.email, tp.phone, tp.knkey FROM joinpersontransition jpt INNER JOIN "
                + " t_personal tp on jpt.idpersona = tp.id WHERE jpt.idtransition_p = ? ");
        SELECCIONAR_DATAPERSONAL_BYIDP = qryBuilder.toString();
        
                
        qryBuilder = new StringBuilder();
        qryBuilder.append(" update joindocumenttransition set details = ? where iddocument = ? and idtransition = ?; ");
        UPDATE_DETAILS_EN_JOINDOCUMENTTRANSITION = qryBuilder.toString();
        
        
        qryBuilder = new StringBuilder();
//        qryBuilder.append(" SELECT vobo FROM joindocumenttransition WHERE iddocument = ? AND idtransition = ? ");
        qryBuilder.append("SELECT iddocument,vobo FROM joindocumenttransition");
        qryBuilder.append( " WHERE (iddocument BETWEEN 1  AND 8)");
        qryBuilder.append( " AND idtransition = ?");
        qryBuilder.append(" ORDER BY iddocument");
        SELECT_VOBO_EN_JOINDOCUMENTTRANSITION = qryBuilder.toString();
                
                
        qryBuilder = new StringBuilder();
        qryBuilder.append("SELECT ifowner "+
            "FROM t_assign "+            
            " WHERE "+
            " knkey = ? ");
        SELECCIONAR_IFOWNERFROMKNKEY = qryBuilder.toString();
   
    

        qryBuilder = new StringBuilder();
        qryBuilder.append("SELECT status, log FROM t_transitions ");
        SELECCIONAR_DATATRANSITION = qryBuilder.toString();
     
    

        qryBuilder = new StringBuilder();
        qryBuilder.append("SELECT status FROM t_transitions WHERE knkey = ? ");
        SELECCIONAR_STATUSTRANSITION = qryBuilder.toString();
    

        qryBuilder = new StringBuilder();
        qryBuilder.append("SELECT statusimple FROM t_transitions WHERE knkey = ? ");
        SELECCIONAR_STATUSIMPLEMENTATION = qryBuilder.toString();
     
        
        qryBuilder = new StringBuilder();
        qryBuilder.append("SELECT id FROM t_transitions WHERE knkey = ? ");
        SELECCIONAR_DATATRANSITIONIDKNKEY = qryBuilder.toString();
        
        qryBuilder = new StringBuilder();
        qryBuilder.append("SELECT id, knkey, status FROM t_transitions WHERE  knkey = ?");
        SELECCIONAR_IDKNKEY = qryBuilder.toString();
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append("SELECT d.name, d.id FROM t_documents d "
                + " INNER JOIN joindocumenttransition j on d.id=j.iddocument "
                + " WHERE j.idtransition = ?");
        SELECCIONAR_DOCUMENTNAME = qryBuilder.toString();
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append("SELECT j.vobo, j.uploaded, j.completiondate, j.details FROM t_documents d "
                + "INNER JOIN joindocumenttransition j on d.id=j.iddocument "
                + "WHERE j.idtransition = ? ");
        SELECCIONAR_JOINDOCUMENTTRANSITIONDATA = qryBuilder.toString();
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append("SELECT iddocument,details from joindocumenttransition where (iddocument = ? or iddocument = ? ) and idtransition = ? order by iddocument");
        SELECCIONAR_JOINDOCUMENTTRANSITIONDETAIL = qryBuilder.toString();
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append("select p.bf, p.name, p.email, p.phone "
                + "FROM t_personal p INNER JOIN joinpersontransition j ON p.id = j.idpersona "
                + "WHERE j.idtransition_p = ? ");
        SELECCIONAR_DATAPERSONAL = qryBuilder.toString();
        
        qryBuilder = new StringBuilder();
        qryBuilder.append("SELECT id FROM t_documents WHERE name = ? ");
        SELECCIONAR_IDFROMDOCUMENTSBYNAME = qryBuilder.toString();
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append(" SELECT id,iddocument,idtransition,vobo,uploaded,completiondate,details FROM "
                + "joindocumenttransition WHERE iddocument = ? AND idtransition = ?");
        SELECCIONAR_IDSFROMJOINDOCUMENTTRANSITION = qryBuilder.toString();

        
        qryBuilder = new StringBuilder();
        qryBuilder.append(" INSERT INTO joindocumenttransition (iddocument, idtransition) VALUES (?, ? )  "
                + " returning id ");
        INSERTAR_RELACION_EN_JOINDOCUMENTTRANSITION = qryBuilder.toString();
        
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append(" UPDATE joindocumenttransition SET vobo= ?, completiondate=? WHERE iddocument= ? AND idtransition= ? ; ");
        UPDATE_VOBO_EN_JOINDOCUMENTTRANSITION = qryBuilder.toString();
        
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append(" UPDATE t_transitions SET log = ? WHERE t_transitions.id = ? ");
        UPDATE_LOGTRANSITION = qryBuilder.toString();
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append(" UPDATE t_transitions SET status = ?, \"lastDateUpdateEstatus\" = ? , \"lastUserUpdateEstatus\" = ? WHERE t_transitions.id = ? ");
        UPDATE_STATUS = qryBuilder.toString();
        
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append(" UPDATE t_transitions SET statusimple = ?, \"lastUserUpdateEstatusImplementation\" = ?, \"lastDateUpdateEstatusImplementation\" = now() WHERE t_transitions.id = ? ");
        UPDATE_STATUS_IMPLE = qryBuilder.toString();
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append(" INSERT INTO t_personal (bf, name, email, phone, knkey) VALUES ( ?, ?, ?, ?, ? ) ");
        INSERTAR_PERSONAL = qryBuilder.toString();
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append(" select (name) from t_personal ");
        SELECCIONAR_NOMBRE_PERSONAL = qryBuilder.toString();
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append(" select * from t_personal where knkey = ? ");
        SELECCIONAR_NOMBRE_PERSONAL_BYKNKEY_TRANSITION = qryBuilder.toString();
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append("SELECT * FROM t_personal WHERE name = ? ");
        SELECCIONAR_DATAPERSONAL_BYNAME = qryBuilder.toString();
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append("SELECT * FROM t_personal WHERE knkey = ? ");
        SELECCIONAR_DATAPERSONAL_BYKNKEY = qryBuilder.toString();
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append("SELECT * FROM t_personal WHERE id = ? ");
        SELECCIONAR_DATAPERSONAL_BYKNID = qryBuilder.toString();
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append(" INSERT INTO joinpersontransition ( idpersona, idtransition_p ) VALUES ( ?, ? ) ");
        INSERT_JOINPERSONALTRANSITION = qryBuilder.toString();
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append(" SELECT * FROM t_personal WHERE email = ? ");
        SELECCIONAR_DATAPERSONAL_BY_EMAIL = qryBuilder.toString();
        
       
        qryBuilder = new StringBuilder();
        qryBuilder.append(" DELETE FROM t_personal WHERE id = ? ");
        ELIMINAR_PERSONAL_BY_ID = qryBuilder.toString();
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append(" DELETE FROM joinpersontransition WHERE idpersona = ? AND idtransition_p = ? ");
        ELIMINAR_JOINPERSONTRANSITION = qryBuilder.toString();
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append(" SELECT COUNT(idpersona) FROM joinpersontransition WHERE idpersona = ? ");
        VERIFICA_COUNT_RELACION_JOINPERSONTRANSITION = qryBuilder.toString();
        
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append(" SELECT log FROM t_transitions WHERE knkey = ? AND \"tipoTransition\" = ? ");
        OBTENER_TRANSITION_LOG = qryBuilder.toString();
        
        qryBuilder = new StringBuilder();
        qryBuilder.append(" UPDATE joindocumenttransition SET vobo= ? , completiondate= now() WHERE iddocument= ? AND idtransition= ? ; ");
        UPDATE_VOBO_CHECKBOX = qryBuilder.toString();
        
        qryBuilder = new StringBuilder();
        qryBuilder.append("select joindocumenttransition.iddocument,joindocumenttransition.id, joindocumenttransition.uploaded from joindocumenttransition");
        qryBuilder.append(" INNER JOIN t_documents");
        qryBuilder.append(" on(joindocumenttransition.iddocument = t_documents.\"id\")");
        qryBuilder.append(" where joindocumenttransition.idtransition = (SELECT id FROM t_transitions WHERE knkey = ? AND t_transitions.\"tipoTransition\" = ?)");
        qryBuilder.append(" ORDER BY joindocumenttransition.iddocument");
        SELECCIONAR_FILES_UPLOADED = qryBuilder.toString();
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append("UPDATE t_transitions SET \"lastDateUpdateComment\" = ?, \"lastUserUpdateComment\" = ?  where id= ? and knkey= ?");
        UPDATE_LAST_DATE_COMMENT = qryBuilder.toString();
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append("select \"lastDateUpdateEstatus\" from t_transitions  where id= ?");
        OBTENER_ULTIMA_FECHA_ESTATUS = qryBuilder.toString();
        
        qryBuilder = new StringBuilder();
        qryBuilder.append("select \"lastDateUpdateComment\" from t_transitions where id = ? and knkey=?");
        
        OBTENER_ULTIMA_FECHA_COMMENT = qryBuilder.toString();
        
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append(" UPDATE t_transitions SET \"lastDateUpdateRequeri\" = now(), \"lastUserUpdateRequeri\" = ? WHERE t_transitions.id = ? and  t_transitions.knkey = ?");
        UPDATE_LAST_UPDATE_REQUI = qryBuilder.toString();
        
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append(" Select \"lastDateUpdateRequeri\" from t_transitions WHERE t_transitions.id = ? and  t_transitions.knkey = ?");
        OBTENER_ULTIMA_FECHA_REQUI = qryBuilder.toString();
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append(" SELECT count(*) FROM t_transitions where knkey= ? ");
        CHECAR_EXISTENCIA_TRANSITION = qryBuilder.toString();
        
        
        qryBuilder = new StringBuilder();
        qryBuilder.append(" UPDATE joindocumenttransition SET uploaded = TRUE,details = ? WHERE iddocument = ? AND idtransition = ? ");
        UPDATE_UPLOAD_FILE = qryBuilder.toString();
        
      }
}
